<?php

class ReportsController extends Controller
{
	/**
	 * @var string the default layout for the views. Defaults to '//layouts/column2', meaning
	 * using two-column layout. See 'protected/views/layouts/column2.php'.
	 */
	public $layout='//layouts/column1';

	/**
	 * @return array action filters
	 */
	public function filters()
	{
		return array(
			'accessControl', // perform access control for CRUD operations
			'postOnly + delete', // we only allow deletion via POST request
		);
	}

	/**
	 * Specifies the access control rules.
	 * This method is used by the 'accessControl' filter.
	 * @return array access control rules
	 */
	public function accessRules()
	{
		return array(
		array('allow',  // allow all users to perform 'index' and 'view' actions
				'actions'=>array('index','view','create','update','admin','LoadExpenseForm','getreceiptbyexpenseId','InsertNewReport','UpdateReport','openreport','NewReport','search','DeleteReport'),
				'users'=>array('*'),
		),
		array('allow', // allow authenticated user to perform 'create' and 'update' actions
				'actions'=>array('create','update'),
				'users'=>array('@'),
		),
		array('allow', // allow admin user to perform 'admin' and 'delete' actions
				'actions'=>array('admin','delete'),
				'users'=>array('admin'),
		),
		array('deny',  // deny all users
				'users'=>array('*'),
		),
		);
	}

	/**
	 * Displays a particular model.
	 * @param integer $id the ID of the model to be displayed
	 */
	public function actionView($id)
	{
		$this->render('view',array(
			'model'=>$this->loadModel($id),
		));
	}

	/**
	 * Creates a new model.
	 * If creation is successful, the browser will be redirected to the 'view' page.
	 */
	public function actionCreate()
	{
		$model=new Report;

		// Uncomment the following line if AJAX validation is needed
		// $this->performAjaxValidation($model);

		if(isset($_POST['Report']))
		{
			$model->attributes=$_POST['Report'];
			if($model->save())
			$this->redirect(array('view','id'=>$model->ReportId));
		}

		$this->render('create',array(
			'model'=>$model,
		));
	}

	/**
	 * Updates a particular model.
	 * If update is successful, the browser will be redirected to the 'view' page.
	 * @param integer $id the ID of the model to be updated
	 */
	public function actionUpdate($id)
	{
		$model=$this->loadModel($id);

		// Uncomment the following line if AJAX validation is needed
		// $this->performAjaxValidation($model);

		if(isset($_POST['Report']))
		{
			$model->attributes=$_POST['Report'];
			if($model->save())
			$this->redirect(array('view','id'=>$model->ReportId));
		}

		$this->render('update',array(
			'model'=>$model,
		));
	}

	/**
	 * Deletes a particular model.
	 * If deletion is successful, the browser will be redirected to the 'admin' page.
	 * @param integer $id the ID of the model to be deleted
	 */
	public function actionDelete($id)
	{
		$this->loadModel($id)->delete();

		// if AJAX request (triggered by deletion via admin grid view), we should not redirect the browser
		if(!isset($_GET['ajax']))
		$this->redirect(isset($_POST['returnUrl']) ? $_POST['returnUrl'] : array('admin'));
	}

	/**
	 * Lists all models.
	 */
	public function actionIndex()
	{
		
		$pageSize = $pageSize = Yii::app()->params['reportPageSize'];
		$page=1;
		$userId = Yii::app()->user->id;
		$defaultCurrency  = Yii::app()->db->createCommand("EXEC stp_GetDefaultCurrencyByUserId $userId")->queryAll();
		$rows = $this->getSearchResults($page);
		$this->render('index',array(
			'rows'=>$rows,
			'page'=>$page,
			'limit'=>$pageSize,
			'totalItem'=>isset($rows[0]['TotalCount'])?$rows[0]['TotalCount']:0,
		    'defaultCurrency'=>isset($defaultCurrency[0])?$defaultCurrency[0]: array('CurrencyName'=>'','ExchangeRate'=>'')	
		));
	}
	/**
	 * 
	 * Searching the report
	 */
    public function actionSearch()
    {
    	$this->layout='//layouts/main';
    	$pageSize = $pageSize = Yii::app()->params['reportPageSize'];
		$page=1;
		$rows = $this->getSearchResults($page);
		$this->render('search',array(
			'rows'=>$rows,
			'page'=>$page,
			'limit'=>$pageSize,
			'totalItem'=>isset($rows[0]['TotalCount'])?$rows[0]['TotalCount']:0		
		));
    }
	/**
	 * Manages all models.
	 */
	public function actionAdmin()
	{
		$model=new Report('search');
		$model->unsetAttributes();  // clear any default values
		if(isset($_GET['Report']))
		$model->attributes=$_GET['Report'];

		$this->render('admin',array(
			'model'=>$model,
		));
	}

	/**
	 * Returns the data model based on the primary key given in the GET variable.
	 * If the data model is not found, an HTTP exception will be raised.
	 * @param integer $id the ID of the model to be loaded
	 * @return Report the loaded model
	 * @throws CHttpException
	 */
	public function loadModel($id)
	{
		$model=Report::model()->findByPk($id);
		if($model===null)
		throw new CHttpException(404,'The requested page does not exist.');
		return $model;
	}

	/**
	 * Performs the AJAX validation.
	 * @param Report $model the model to be validated
	 */
	protected function performAjaxValidation($model)
	{
		if(isset($_POST['ajax']) && $_POST['ajax']==='report-form')
		{
			echo CActiveForm::validate($model);
			Yii::app()->end();
		}
	}
	/**
	 *
	 * Enter description here ...
	 * @param int $page
	 */
	private function getSearchResults($page=1)
	{
		$reportnameEmail = isset($_POST['reportname_email'])?$_POST['reportname_email']:'';
		$from = isset($_REQUEST['startDate'])?$_REQUEST['startDate']: date('Y-m-d G:i:s', PHP_INT_MAX + 1);
		$to = isset($_REQUEST['endDate'])?$_REQUEST['endDate']+' 23:59:59.999': date('Y-m-d G:i:s', PHP_INT_MAX);
		$filterTypeReport = isset($_REQUEST['report_type_search'])?$_REQUEST['report_type_search']:'All';
		$filterTypeSubmitter = isset($_REQUEST['submitter_search'])?$_REQUEST['submitter_search']:'AllSubmitter';
		$filterStatus = isset($_REQUEST['reportStatus'])?$_REQUEST['reportStatus']:'All';
		$pageSize = Yii::app()->params['expensePageSize'];
		$customerId=-1;
		$userId = Yii::app()->user->id;

		$filterSelector = isset($_REQUEST['expense_filter_type'])?$_REQUEST['expense_filter_type']:'unreported';
		$command  = Yii::app()->db->createCommand("EXEC stp_GetAllReportLimited :ReportnameEmail,:From, :To,:FilterTypeReport,:FilterTypeSubmitter,:FilterStatus,:UserId,:PageSize,:Page");
		$command->bindValue(':ReportnameEmail',  $reportnameEmail, PDO::PARAM_STR);
		$command->bindValue(':From',  $from, PDO::PARAM_STR);
		$command->bindValue(':To',  $to, PDO::PARAM_STR);
		$command->bindValue(':FilterTypeReport', $filterTypeReport, PDO::PARAM_STR);
		$command->bindValue(':FilterTypeSubmitter', $filterTypeSubmitter, PDO::PARAM_STR);
		$command->bindValue(':FilterStatus', $filterStatus, PDO::PARAM_STR);
		$command->bindValue(':UserId', $userId, PDO::PARAM_INT);
		$command->bindValue(':PageSize', $pageSize, PDO::PARAM_INT);
		$command->bindValue(':Page', $page, PDO::PARAM_INT);
		$rows = $command->queryAll();
		return $rows!=null?$rows:array();
	}
    /**
	 * Load Expense form
	 */
	public function actionLoadExpenseForm()
	{
		$this->layout='//layouts/main';
		$customers  = Yii::app()->db->createCommand("EXEC stp_GetAllActivedCustomers")->queryAll();
		$currencies  = Yii::app()->db->createCommand("EXEC stp_GetAllActivedCurrencies")->queryAll();
		$categories = Yii::app()->db->createCommand("EXEC stp_GetAllActivedExpenseCategory")->queryAll();
		$units = Yii::app()->db->createCommand("EXEC stp_GetAllActivedUnits")->queryAll();
		$pageSize = $pageSize = Yii::app()->params['expensePageSize'];
		$page=1;
		$from = date('Y-m-d G:i:s', PHP_INT_MAX + 1);
		$to =  date('Y-m-d G:i:s', PHP_INT_MAX);
		$expenseStatus = 'All';
		$category =-1;
		$pageSize = 8;
		$userId = Yii::app()->user->id;
		$filterSelector = 'unreported';
		$command  = Yii::app()->db->createCommand("EXEC stp_GetAllExpenseLimited :From, :To,:FilterSelector,:ExpenseStatus,:Category,:UserId,:PageSize,:Page");
		$command->bindValue(':From',  $from, PDO::PARAM_STR);
		$command->bindValue(':To',  $to, PDO::PARAM_STR);
		$command->bindValue(':FilterSelector', $filterSelector, PDO::PARAM_STR);
		$command->bindValue(':ExpenseStatus', $expenseStatus, PDO::PARAM_STR);
		$command->bindValue(':UserId', $userId, PDO::PARAM_INT);
		$command->bindValue(':Category', $category, PDO::PARAM_INT);
		$command->bindValue(':PageSize', $pageSize, PDO::PARAM_INT);
		$command->bindValue(':Page', $page, PDO::PARAM_INT);
		$rows = $command->queryAll();
		

		$this->render('loadExpenseForm',array(
		    'customers'=>$customers,
		    'currencies'=>$currencies,
			'categories'=>$categories,
			'units'=>$units,
			'rows'=>$rows,
			'page'=>$page,
			'limit'=>$pageSize,
			'totalItem'=>isset($rows[0]['TotalCount'])?$rows[0]['TotalCount']:0
		));
	}
	/**
	 * 
	 * Get Receipt by Expense Id
	 */
	public function actionGetReceiptByExpenseId()
	{
		$this->layout='//layouts/main';
		$receiptId = $_POST['receiptId'];
		$date = $_POST['date'];
		$currency = $_POST['currency'];
		$total = $_POST['total'];
		$category = $_POST['category'];
		$command = Yii::app()->db->createCommand("EXEC stp_GetReceiptByExpenseId :ReceiptId");
		$command->bindValue(':ReceiptId', $receiptId, PDO::PARAM_INT);
		$receipt = $command->queryAll();
		if(!empty($receipt))
		{
			$this->render('receiptByExpenseId',array(
				'id'=>$receipt[0]['Id'],
			    'url'=>$receipt[0]['Url'],
				'date'=>$date,
				'currency'=>$currency,
				'total'=>$total,
				'category'=>$category,
				'total'=>$total,		    		
			));
		}
	}
	/**
	 * 
	 * Insert a new report
	 */
	public function  actionInsertNewReport()
	{
		if(isset( $_POST['report_type']))
		{
			$reportType = $_POST['report_type'];
			$fromEmail = $_POST['fromEmail'];
			$toEmail = $_POST['toEmail'];
			$reportName =$_POST['reportName'];
			$status = $_POST['status'];
			$userId = $_POST['userId'];
			$submitter= $userId;
			$total= $_POST['totals'];
			$expenseString = $_POST['expenseString'];
			$receiptString = $_POST['receiptString'];
			$reportId = null;
			$command = Yii::app()->db->createCommand("EXEC stp_InsertNewReport :ReportType,:FromEmail,:ToEmail,:ReportName,:Status,:UserId,:Submitter,:Total,:ExpenseString,:ReceiptString,:ReportId");
			$command->bindValue(':ReportType', $reportType, PDO::PARAM_STR);
			$command->bindValue(':FromEmail', $fromEmail, PDO::PARAM_STR);
			$command->bindValue(':ToEmail', $toEmail, PDO::PARAM_STR);
			$command->bindValue(':ReportName', $reportName, PDO::PARAM_STR);
			$command->bindValue(':Status', $status, PDO::PARAM_STR);
			$command->bindValue(':UserId', $userId, PDO::PARAM_INT);
			$command->bindValue(':Submitter', $submitter, PDO::PARAM_STR);
			$command->bindValue(':Total', $total, PDO::PARAM_STR);
			$command->bindValue(':ExpenseString', $expenseString, PDO::PARAM_STR);
			$command->bindValue(':ReceiptString', $receiptString, PDO::PARAM_STR);
			$command->bindParam(':ReportId', $reportId, PDO::PARAM_INT | PDO::PARAM_INPUT_OUTPUT, 11);	
			$command->execute();
			echo $reportId;
		}
	}
	/**
	 * 
	 * Insert a new report
	 */
	public function  actionUpdateReport()
	{
		if(isset($_POST['reportIdHidden'])&&$_POST['reportIdHidden']!='-1'&&$_POST['reportIdHidden']!='')
		{
			$reportType = $_POST['report_type'];
			$fromEmail = $_POST['fromEmail'];
			$toEmail = $_POST['toEmail'];
			$reportName =$_POST['reportName'];
			$status = $_POST['status'];
			$submitter= $_POST['userId'];
			$total= $_POST['totals'];
			$expenseString = $_POST['expenseString'];
			$receiptString = $_POST['receiptString'];
			$reportId = $_POST['reportIdHidden'];
			$command = Yii::app()->db->createCommand("EXEC stp_UpdateReport :ReportType,:FromEmail,:ToEmail,:ReportName,:Status,:Submitter,:Total,:ExpenseString,:ReceiptString,:ReportId");
			$command->bindValue(':ReportType', $reportType, PDO::PARAM_STR);
			$command->bindValue(':FromEmail', $fromEmail, PDO::PARAM_STR);
			$command->bindValue(':ToEmail', $toEmail, PDO::PARAM_STR);
			$command->bindValue(':ReportName', $reportName, PDO::PARAM_STR);
			$command->bindValue(':Status', $status, PDO::PARAM_STR);
			$command->bindValue(':Submitter', $submitter, PDO::PARAM_STR);
			$command->bindValue(':Total', $total, PDO::PARAM_STR);
			$command->bindValue(':ExpenseString', $expenseString, PDO::PARAM_STR);
			$command->bindValue(':ReceiptString', $receiptString, PDO::PARAM_STR);			
			$command->bindValue(':ReportId', $reportId, PDO::PARAM_INT);	
			$command->execute();
			echo $reportId;
		}
	}
	/**
	 * 
	 * Open the report
	 */
	public function actionOpenReport()
	{
		$this->layout='//layouts/main';
		if(isset($_POST['reportId']))
		{
			$reportId = $_POST['reportId'];
			$userId = Yii::app()->user->id;
			$report = Yii::app()->db->createCommand("EXEC stp_GetReportById $reportId")->queryAll();
			$defaultCurrency = NULL;
			$command = Yii::app()->db->createCommand("EXEC stp_GetReportDetailByReportId :ReportId,:DefaultCurrency");
			$command->bindValue(':ReportId', $reportId, PDO::PARAM_STR);
			$command->bindParam(':DefaultCurrency', $defaultCurrency, PDO::PARAM_STR | PDO::PARAM_INPUT_OUTPUT,11);	
			$reportDetails = $command->queryAll();
			$receipts = Yii::app()->db->createCommand("EXEC stp_GetReceiptByReportId $reportId")->queryAll();
			
			
			$this->render('reportDetail',array(
				'report'=>$report[0],
			    'reportDetails'=>$reportDetails,
				'receipts'=>$receipts,
			    'defaultCurrency'=>$defaultCurrency
			));
			
			
		}
	}

	public function actionNewReport()
	{
		$this->layout='//layouts/main';
		$this->render('newReport');
	}
	/**
	 * 
	 * Delete the reports checked
	 * @throws CHttpException
	 */
	public function actionDeleteReport()
	{
		if(isset($_POST['deleteString']))
		{
			$deleteString = $_POST['deleteString'];
			$command = Yii::app()->db->createCommand("EXEC stp_DeleteReport :deleteString");
			$command->bindValue(':deleteString', $deleteString, PDO::PARAM_STR);
			$command->execute();
		}
	}
}
